package factory.one.service;

import factory.one.entity.SysColumn;
import factory.one.entity.SysTable;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

/**
 * @author 支绍昆
 * 2023/1/29
 * 数据库管理
 **/
@Service
public class DatabaseService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<SysTable> getAllTables() {
        return jdbcTemplate.query("select * from SYS.SYSTABLES", (rs, rowNum) -> {
            SysTable table = new SysTable();
            table.setId(rs.getString("TABLEID"));
            table.setName(rs.getString("TABLENAME"));
            table.setSchemaId(rs.getString("SCHEMAID"));
            table.setType(rs.getString("TABLETYPE"));
            table.setLockGranularity(rs.getString("LOCKGRANULARITY"));
            return table;
        });
    }

    public List<SysTable> getUserTables() {
        List<SysTable> list = getAllTables();
        list.removeIf(e -> e.getType().equals("S"));
        return list;
    }

    public List<SysColumn> descTable(String tableId) {
        return jdbcTemplate.query("select * from SYS.SYSCOLUMNS where REFERENCEID = '" + tableId + "' order by COLUMNNUMBER"
                , (rs, rowNum) -> {
                    SysColumn column = new SysColumn();
                    column.setReferenceId(rs.getString("REFERENCEID"));
                    column.setName(rs.getString("COLUMNNAME"));
                    column.setNumber(rs.getInt("COLUMNNUMBER"));
                    column.setDataType(rs.getString("COLUMNDATATYPE"));
                    column.setDefaultId(rs.getString("COLUMNDEFAULTID"));
                    return column;
                });
    }

    public void showResult(String sql) {
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        for (Map<String, Object> m : maps) {
            System.out.println();
            for (Map.Entry<String, Object> entry : m.entrySet()) {
                System.out.print("[" + entry.getKey() + ":" + entry.getValue() + "] ");
            }
        }
    }

    public void execute(String sql) {
        jdbcTemplate.execute(sql);
    }

}
